/**
 * Project: lionbule-other
 * 
 * File Created at 2012-6-28
 * $Id$
 * 
 * Copyright 1999-2100 Alibaba.com Corporation Limited.
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of
 * Alibaba Company. ("Confidential Information").  You shall not
 * disclose such Confidential Information and shall use it only in
 * accordance with the terms of the license agreement you entered into
 * with Alibaba.com.
 */
package com.dm;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

/**
 * DataModel.
 * 
 * @author LionBule 2012-6-28 下午8:53:12
 */
public class DataModel {

    public static void main(String[] args) throws Exception {
        String filepath = "E:/work/贷后预警迭代5/DM-junjia-(V2.1) - 副本.xlsx";
        String storePath = "E:/work/贷后预警迭代5/A_tmall.sql";
        String[] sheetArray = new String[] { "PID"};

        // do
        List<String> sqls = doAction(sheetArray, filepath);
        write(sqls, storePath);

        System.out.println("ok");
    }

    @SuppressWarnings("restriction")
    static void write(List<String> sqls, String storePath) throws IOException {
        FileWriter fw = new FileWriter(storePath);
        String lineSeparator = (String) java.security.AccessController
                .doPrivileged(new sun.security.action.GetPropertyAction("line.separator"));

        for (String s : sqls) {
            fw.write(s);
            fw.write(lineSeparator);
        }

        fw.close();
    }

    static List<String> doAction(String[] sheetArray, String filepath) throws Exception {
        List<String> sqls = new ArrayList<String>();

        Map<String, Integer> paths = PathService.queryAllPath();
        Map<String, Integer> typeMapping = getTypeMapping();

        Workbook wb = WorkbookFactory.create(new File(filepath));
        for (String sheetName : sheetArray) {
            Sheet tmpSheet = wb.getSheet(sheetName);
            for (Row row : tmpSheet) {
                int rownum = row.getRowNum();
                if (rownum == 0) {
                    continue;
                }
                double index = row.getCell(0).getNumericCellValue();
                if (index < 1) {
                    continue;
                }
                String name = row.getCell(1).getStringCellValue().trim();
                String tmpPath = row.getCell(2).getStringCellValue().trim();
                String code = row.getCell(3).getStringCellValue().trim();
                String type = row.getCell(4).getStringCellValue().trim().toUpperCase();
                String tabName = "";
                String colName = "";
                if (!sheetName.equals("Realtime")) {
                    tabName = row.getCell(5).getStringCellValue().trim().toUpperCase();
                    colName = row.getCell(6).getStringCellValue().trim().toUpperCase();
                }

                String errMsg = new StringBuilder().append(name).append(",").append(tmpPath)
                        .append(",").append(code).append(",").append(type).append(",")
                        .append(tabName).append(",").append(colName).toString();

                Integer parentId = paths.get(tmpPath);
                if (parentId == null || parentId <= 0) {
                    throw new IllegalArgumentException("parentId is error, " + errMsg);
                }
                Integer typeNum = typeMapping.get(type);
                if (typeNum == null) {
                    throw new IllegalArgumentException("typeNum is null. " + errMsg + ", types="
                            + typeMapping);
                }

                String sql = getSql(parentId, code, name, typeNum, tabName, colName);
                sqls.add(sql);
            }
        }

        return sqls;
    }

    static String getSql(int parentId, String code, String name, int type, String tableName,
                         String colName) {
        StringBuilder sb = new StringBuilder();
        sb.append(
                "insert into agds_dm_property (path_id,pcode,pname,data_type,dw_table,dw_column,status,creater,operator,gmt_create,gmt_modified,descs)")
                .append(" values (").append(parentId).append(",'").append(code).append("','")
                .append(name).append("',").append(type).append(",'").append(tableName)
                .append("', '").append(colName)
                .append("',3,'qingyan.maqy','qingyan.maqy',now(),now(),'desc'")
                .append(");");
        return sb.toString();
    }

    static Map<String, Integer> getTypeMapping() {
        Map<String, Integer> rs = new HashMap<String, Integer>();
        rs.put("Long".toUpperCase(), 1);
        rs.put("Double".toUpperCase(), 2);
        rs.put("String".toUpperCase(), 3);
        rs.put("Boolean".toUpperCase(), 4);
        rs.put("Date".toUpperCase(), 5);
        rs.put("Calendar".toUpperCase(), 5);
        rs.put("Integer".toUpperCase(), 6);

        return rs;
    }
}
